<?php
namespace app\index\controller;
use app\common\controller\Base;
use app\common\model\Data as DataModel;
use app\common\model\Datee;
use app\common\model\CarModel;
use think\Session;
use think\Db;

class Data extends Base
{
	public function dataList(){
		$res = Db::query("select a.data_id,sum(a.wholesale) as wholesale,sum(a.retail) as retail,"
            ."d.id as did,d.name as dname,"
            ."e.* "
            ."from my_data as a "
            ."inner join car_model as b on a.model_id = b.id "
            ."inner join car_model as c on b.pid = c.id "
            ."inner join car_model as d on c.pid = d.id "
        	."inner join my_date as e on a.date_id = e.date_id "
        	."group by yy,mm,ww,did");

        $userName = Session::get('user_info.username');

        if ($userName == 'admin') {
            $oem = CarModel::where("pid",0)->select();
        } else {
            $map['name'] = Session::get('user_info.oem');
            $oem = CarModel::where($map)->select();
        }

		$this->view->assign('title', "周度数据");
		$this->view->assign('list', $res);
        $this->view->assign('oem', $oem);
		$this -> view -> count = sizeof($res);
		return $this -> view -> fetch('data_list');
    }
    
    
    public function dataEditView(){
    	$data =  $this->request->param();

    	$res = Db::query("select a.data_id,a.wholesale,a.retail,"
			."b.id as bid,b.name as bname,"
			."c.id as cid,c.name as cname,"
			."d.id as did,d.name as dname,"
			."e.* "
			."from my_data as a "
			."right join car_model as b on a.model_id = b.id "
			."inner join car_model as c on b.pid = c.id "
			."inner join car_model as d on c.pid = d.id "
			."inner join my_date as e on a.date_id = e.date_id "
			."where yy = ? and mm = ? and ww = ? and d.name = ?",[$data['yy'],$data['mm'],$data['ww'],$data['dname']]);

    	$res_group = Db::query("select sum(a.wholesale) as ws,sum(a.retail) as re,"
			."d.id as did,d.name as dname,"
			."e.* "
			."from my_data as a "
			."inner join car_model as b on a.model_id = b.id "
			."inner join car_model as c on b.pid = c.id "
			."inner join car_model as d on c.pid = d.id "
			."inner join my_date as e on a.date_id = e.date_id "
			."where yy = ? and mm = ? and ww = ? and d.name = ? "
			."group by d.name",[$data['yy'],$data['mm'],$data['ww'],$data['dname']]);

    	$list_com = [];
    	foreach ($res as $key => $value) {
    		# code...
    		$list_com[] = [
    			"data_id" => $value['data_id'],
    			"yy" => $value['yy'],
    			"mm" => $value['mm'],
    			"ww" => $value['ww'],
    			"bid" => $value['bid'],
    			"bname" => $value['bname'],
    			"cname" => $value['cname'],
    			"dname" => $value['dname'],
    			"wholesale" => $value['wholesale'],
    			"retail" => $value['retail'],
    			"wholesale_name" => 'wholesale_' . $value['data_id'],
    			"retail_name" => 'retail_' . $value['data_id'],
    		];
    		
    	}
    	
    	$this->view->assign('list_group', $res_group[0]);
    	$this->view->assign('list_data', $data);
    	$this->view->assign('list', $list_com);
    	return $this -> view -> fetch('data_edit');

    }

    public function editData(){
    	$data = $this->request->param();

    	foreach ($data as $key => $value) {
			$pos = strpos($key,'_');
			$typee = substr($key,0,$pos);
			$data_id = substr($key,$pos+1);
			
			if ($typee == "wholesale") {
				$map["wholesale"] = $value;
			}else {
				$map["retail"] = $value;
			}

			$condition = ['data_id'=>$data_id] ;
        	$result = DataModel::update($map, $condition);

		}

    	if (true == $result) {
            return ['status'=>1, 'message'=>'更新成功'];
        } else {
            return ['status'=>0, 'message'=>'更新失败,请检查'];
        }
    }

    public function deleteData(){
    	$data =  $this->request->param();

        // return $data_p;
        $maap['yy'] = $data['yy'];
        $maap['mm'] = $data['mm'];
        $maap['ww'] = $data['ww'];
        $date_res = Datee::where($maap)->find();
        $date_id = $date_res['date_id'];

        Db::execute("delete from my_data where "
                    ."date_id = ? and model_id in "
                    ."(select a.id as model_id "
                    ."from car_model as a "
                    ."left join car_model as b on a.pid = b.id "
                    ."left join car_model as c on b.pid = c.id "
                    ."where c.name = ?)",[$date_id,$data['oem']]);
        return $data;
    }

    public function dataAddView(){
        $data =  $this->request->param();
        // $data['oem'] = 1;
        // var_dump($data);
        $res = Db::query("select a.id as aid,a.name as aname,b.name as bname "
                    . "from car_model as a "
                    . "left join car_model as b on a.pid = b.id "
                    . "left join car_model as c on b.pid = c.id "
                    . "where c.name = ? order by bname,aname",[$data['oem']]);

        $res_list = [];
        foreach ($res as $key => $value) {
            # code...
            $res_list[] = [
                "aid" => $value['aid'],
                "aname" => $value['aname'],
                "bname" => $value['bname'],
                "wholesale_name" => 'wholesale_' . $value['aid'],
                "retail_name" => 'retail_' . $value['aid'],
            ];
            
        }


        // var_dump($res);
        $this->view->assign('list_data', $data);
        $this->view->assign('res', $res_list);
        return $this -> view -> fetch('data_add');
    }

    public function addData(){
        $data = $this->request->param();

        $data_p = $this->request->post();

        // return $data_p;
        $maap['yy'] = $data['yy'];
        $maap['mm'] = $data['mm'];
        $maap['ww'] = $data['ww'];

        $date_res = Datee::where($maap)->find();

        $k = 0;
        $ids = 0;
        foreach ($data_p as $key => $value) {
            # code...
            $pos = strpos($key,'_');
            $typee = substr($key,0,$pos);  //得到字段类型，批发还是零售
            $model_id = substr($key,$pos+1);  //得到车型id

            $arr[$k]['model_id'] = $model_id;
            if ($typee == 'wholesale') {
                $arr[$k]['wholesale'] = ($value=="") ? 0 : $value;
            }else{
                $arr[$k]['retail'] = ($value=="") ? 0 : $value;
            }
            $ids += 1;
            if ($ids == 2) {
                $k+=1;
                $ids =0;
            }
        }
        // return $arr;
        $result = "";
        
        foreach ($arr as $key => $value) {
            $check = DataModel::where(['date_id'=>$date_res['date_id'],'model_id'=>$value['model_id']])->find();
            // return ($result);
            if (empty($check)) {
                $result = DataModel::create([
                    'date_id'=>$date_res['date_id'],
                    'model_id'=>$value['model_id'],
                    'wholesale'=>$value['wholesale'],
                    'retail'=>$value['retail']
                ]);
            };
        };
        
        $message = ['status'=>0, 'message'=>'厂家数据可能已经存在,请检查'];

        if ($result !== "") {
            $message = ['status'=>1, 'message'=>'厂家数据更新成功'];
        };

        return $message;
    }

    public function dataDownload(){
        return $this -> view -> fetch('data_download');
    }
    
    public function downloadData(){
        $data = $this->request->param();
        return $this -> view -> fetch('data_download');
    }
    
    public function upload(){
        // 获取表单上传文件 例如上传了001.jpg
        $file = request()->file('excelfile');

        // 移动到框架应用根目录/public/uploads/ 目录下
        if($file){
            $info = $file->validate(['ext'=>'xls,xlsx,xlsb'])
            ->move( ROOT_PATH . 'public' . DS . 'uploads');
            if($info){
                // 成功上传后 获取上传信息
                // 输出 jpg
                echo $info->getExtension();
                // 输出 20160820/42a79759f284b767dfcb2a0197904287.jpg
                echo $info->getSaveName();
                // 输出 42a79759f284b767dfcb2a0197904287.jpg
                echo $info->getFilename();
            }else{
                // 上传失败获取错误信息
                echo $file->getError();
            }
        }
    }

    public function excel()
    {
        if (request()->isPost()) {
            //处理Excel文件请求
            vendor("PHPExcel.PHPExcel");
            $file = request()->file('file');
            if (is_null($file)) {
                return $this->success("上传文件不能为空~", 'index/data/datadownload');
            }
            $info = $file->validate(['size' => 512000, 'ext' => 'xlsx,xls,csv'])->move(ROOT_PATH . 'public' . DS . 'uploads' . DS . 'excel');
            
            if ($info) {
                $exclePath = $info->getSaveName();  //获取文件名
                $file_name = ROOT_PATH . 'public' . DS . 'uploads' . DS . 'excel' . DS . $exclePath;   //上传文件的地址
                $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
                $obj_PHPExcel = $objReader->load($file_name, $encode = 'utf-8');  //加载文件内容,编码utf-8
                echo "<pre>";
                $excel_array = $obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
                array_shift($excel_array);  //删除第一个数组(标题);
                //下面写逻辑代码 ...
            }
        }
        return view();
    }

    public function tryy(){
        $check = DataModel::where(['date_id'=>3,'model_id'=>12])->find();
            if (empty($check)) {
                return "可以新建。";
            }else{
                return "存在了。";
            }
    }



    public function addData132(){
        $dataa = '{"wholesale_10":"","retail_10":"","wholesale_11":"","retail_11":"","wholesale_8":"2","retail_8":"","wholesale_20":"2124","retail_20":"222","wholesale_23":"","retail_23":"","wholesale_9":"","retail_9":"","wholesale_22":"","retail_22":""}';
        $jsonArr = json_decode($dataa, TRUE);
        $maap['yy'] = 2019;
        $maap['mm'] = 9;
        $maap['ww'] = 2;
        
        $date_res = Datee::where($maap)->find();
        
        var_dump($jsonArr);
        echo "<hr>";
        $k = 0;
        $ids = 0;
        foreach ($jsonArr as $key => $value) {
            # code...
            $pos = strpos($key,'_');
            $typee = substr($key,0,$pos);  //得到字段类型，批发还是零售
            $model_id = substr($key,$pos+1);  //得到车型id

            echo $key . "<br>";
            echo (($value=="") ? 0 : $value) . "<br>";
            $arr[$k]['model_id'] = $model_id;
            if ($typee == 'wholesale') {
                $arr[$k]['wholesale'] = ($value=="") ? 0 : $value;
            }else{
                $arr[$k]['retail'] = ($value=="") ? 0 : $value;
            }
            $ids += 1;
            if ($ids == 2) {
                $k+=1;
                $ids =0;
            }
        }

        var_dump($arr);

        echo '<hr>';

        foreach ($arr as $key => $value) {
                echo "date_id:" . $date_res['date_id'];
                echo "model_id:" . $value['model_id'];
                echo "wholesale:" . $value['wholesale'];
                echo "retail:" . $value['retail'];
                echo '<br>';
        };


    }

    public function test1(){

		$res = Db::query("select a.data_id,sum(a.wholesale) as wholesale,sum(a.retail) as retail,"
            ."d.id as did,d.name as dname,"
            ."e.* "
            ."from my_data as a "
            ."inner join car_model as b on a.model_id = b.id "
            ."inner join car_model as c on b.pid = c.id "
            ."inner join car_model as d on c.pid = d.id "
        	."inner join my_date as e on a.date_id = e.date_id "
        	."group by yy,mm,ww,did");

		$list = [];
        foreach ($res as $key => $value) {
            echo($value['yy'] . "\t");
            echo($value['mm'] . "\t");
            echo($value['ww'] . "\t");
            echo($value['dname'] . "\t");
            echo($value['wholesale'] . "\t");
            echo($value['retail'] . "\t");
            // var_dump("<br/>");
            echo("<hr>");
        }

    }

    public function test2(){

    	$res = Db::query("select a.data_id,a.wholesale,a.retail,"
			."b.id as bid,b.name as bname,"
			."c.id as cid,c.name as cname,"
			."d.id as did,d.name as dname,"
			."e.* "
			."from my_data as a "
			."inner join car_model as b on a.model_id = b.id "
			."inner join car_model as c on b.pid = c.id "
			."inner join car_model as d on c.pid = d.id "
			."inner join my_date as e on a.date_id = e.date_id "
			."where yy = ? and mm = ? and ww = ? and d.name = ?",[2019,9,1,"SGM"]);

    	$list_com = [];
    	foreach ($res as $key => $value) {
    		# code...
    		$list_com[] = [
    			"data_id" => $value['data_id'],
    			"yy" => $value['yy'],
    			"mm" => $value['mm'],
    			"ww" => $value['ww'],
    			"wholesale" => $value['wholesale'],
    			"retail" => $value['retail'],
    			"wholesale_name" => 'wholesale_' . $value['data_id'],
    			"retail_name" => 'retail_' . $value['data_id'],
    		];
    		
    	}

    	var_dump($res);
    	var_dump("<hr>");
    	var_dump($list_com);
    	var_dump("<hr>");
    	foreach ($res as $key => $value) {
    		# code...
    		echo($key);
    		echo("\t");
    		var_dump($value);
    		echo("<br/>");
    	};
    	var_dump("<hr>");
    	var_dump($list_com[0]['wholesale']);

    }

    public function test3(){
    	$yy = 2019;
    	$mm = 9;
    	$ww = 1;
    	$dname = "SVW";

    	$date_id = Db::table('my_date')->where('yy',$yy)->where('mm',$mm)->where('ww',$ww)->column('date_id');
    	
    	$res = Db::query("select a.id as model_id "
				. "from car_model as a "
				. "inner join car_model as b on a.pid = b.id "
				. "inner join car_model as c on b.pid = c.id "
				. "where c.name = ?",[$dname]);

    	var_dump($date_id);
    	var_dump($res);
    	echo "<hr>";
    	foreach ($res as $key => $value) {
    		# code...
    		echo($value['model_id'] . "<br/>");
    	}
    	
    	
    }



    
}